package com.apes.scm.masterdata.party.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 功能：接入商查询Dao
 *
 * @Auther: linwz
 * @Date: 2018-12-10 18:22
 */
@Component
public class AccessProviderDao extends ApesDao {

    @Autowired
    SqlService sqlService;

    public List<Map> queryAccessProviderToApp(String id, Long lastTimestamp, int maxNum) {
        String sSQL = " select T.id from mas_access_provider T where 1 = 1 ";
        JSONObject param = new JSONObject();
//        if (approveId!=null){
//            sSQL= sSQL + "and a.approve_id ='"+ approveId +"'";
//        }
        if (id != null){
            sSQL= sSQL + "and t.id ='"+ id +"'";
        }
        if (lastTimestamp != null ){
            sSQL= sSQL + "and t.last_updated_stamp >"+ lastTimestamp  +"/ (1000 * 60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS')";
        }
        if (maxNum != 0 ){
            sSQL= sSQL + "and Rownum <="+ maxNum +"";
        }
        sSQL= sSQL + " ORDER BY t.id,t.last_updated_stamp ASC";
        return this.find(sSQL);
    }


    public JSONArray querySupplierTicketWarningCount(String tableName) {
        String sSql = "SELECT COUNT(1) COUNT FROM " +
                tableName +" A, " + tableName + "_ITEM B " +
                "                   WHERE 1 = 1 " +
                "                     AND A.ID = B.ID " +
                " AND A.STATE = 'exec' " +
                " AND (A.END_DATE <(SYSDATE+15) OR A.END_DATE IS NULL)";

        return toJSONArray(this.find(sSql));
    }

    public JSONArray supplierDimensions(String tableName) {
        String sSql = "SELECT A.UPDATER_ID," +
                "TO_CHAR(A.END_DATE,'yyyy-MM-dd HH24:mi:ss') END_DATE , A.STOP_DATE, A.CONTROL_TYPE, " +
                "B.P101, A.ID, " +
                "                         B.SUPPLIER_ID, " +
                "                         (SELECT S1.NAME " +
                "                             FROM MAS_PARTY_ROLE S0, MAS_PARTY S1 " +
                "                            WHERE S0.PARTY_ID = S1.ID " +
                "                              AND S0.ROLE_ID = 'Supplier' " +
                "                              AND S0.ID = B.SUPPLIER_ID) AS SUPPLIER_ID_NAME " +
                "                    FROM " +
                tableName +" A, " + tableName + "_ITEM B " +
                "                   WHERE 1 = 1 " +
                "                     AND A.ID = B.ID " +
                "                    AND A.STATE = 'exec' AND (A.END_DATE <(SYSDATE+15)OR A.END_DATE IS NULL) " +
                "";
        return toJSONArray(this.find(sSql));
    }

    public JSONArray supplierClassifyDimensions(String tableName) {
        String sSql = "SELECT A.UPDATER_ID, A.PRIORITY, A.START_DATE, A.CREATOR_ID, A.CREATE_DATE, " +
                "           TO_CHAR(A.END_DATE,'yyyy-MM-dd HH24:mi:ss') END_DATE , " +
                "       A.STOP_DATE, A.ID, A.APPROVE_DATE, B.CLASSIFY_ID, " +
                "       (SELECT S0.NAME " +
                "           FROM MAS_CLASSIFY S0 " +
                "          WHERE 1 = 1 " +
                "            AND S0.ID = B.CLASSIFY_ID) AS CLASSIFY_ID_NAME, B.P101, " +
                "       B.SUPPLIER_ID, " +
                "       (SELECT S1.NAME " +
                "           FROM MAS_PARTY_ROLE S0, MAS_PARTY S1 " +
                "          WHERE S0.PARTY_ID = S1.ID " +
                "            AND S0.ROLE_ID = 'Supplier' " +
                "            AND S0.ID = B.SUPPLIER_ID) AS SUPPLIER_ID_NAME " +
                "  FROM " +
                tableName +" A, " + tableName + "_ITEM B " +
                " WHERE 1 = 1  " +
                "   AND A.ID = B.ID" +
                "   AND A.STATE = 'exec' AND (A.END_DATE <(SYSDATE+15)OR A.END_DATE IS NULL) ";
        return toJSONArray(this.find(sSql));
    }

    public JSONArray supplierClassifyBrandDimensions(String tableName) {
        String sSql = "SELECT A.UPDATER_ID, A.PRIORITY, A.START_DATE, A.CREATOR_ID, A.CREATE_DATE, " +
                "       TO_CHAR(A.END_DATE,'yyyy-MM-dd HH24:mi:ss') END_DATE, " +
                "       A.STOP_DATE, A.ID, A.APPROVE_DATE, B.BRAND_ID, " +
                "       (SELECT S0.NAME " +
                "           FROM MAS_BRAND S0 " +
                "          WHERE 1 = 1 " +
                "            AND S0.ID = B.BRAND_ID) AS BRAND_ID_NAME, B.CLASSIFY_ID, " +
                "       (SELECT S0.NAME " +
                "           FROM MAS_CLASSIFY S0 " +
                "          WHERE 1 = 1 " +
                "            AND S0.ID = B.CLASSIFY_ID) AS CLASSIFY_ID_NAME, B.P101, " +
                "       B.SUPPLIER_ID, " +
                "       (SELECT S1.NAME " +
                "           FROM MAS_PARTY_ROLE S0, MAS_PARTY S1 " +
                "          WHERE S0.PARTY_ID = S1.ID " +
                "            AND S0.ROLE_ID = 'Supplier' " +
                "            AND S0.ID = B.SUPPLIER_ID) AS SUPPLIER_ID_NAME " +
                "  FROM " +
                tableName +" A, " + tableName + "_ITEM B " +
                " WHERE 1 = 1  " +
                "   AND A.ID = B.ID" +
                "   AND A.STATE = 'exec' AND (A.END_DATE <(SYSDATE+15)OR A.END_DATE IS NULL) ";
        return toJSONArray(this.find(sSql));
    }

}
